﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace DM4
{
    /// <summary>
    /// <para>Class for read from xml file and save parameters into xml file</para> 
    /// <para>as well as for get and set parameters during DBParams class using</para> 
    /// <para>get and set parametrs by string-key for following datatypes:</para>  
    /// <para>boolean</para> 
    /// <para>datetime</para> 
    /// <para>decimal</para> 
    /// <para>double</para> 
    /// <para>Int64</para> 
    /// <para>Int32</para> 
    /// <para>String</para> 
    /// </summary>
    public class DBParams
    {
        private readonly string _connString;
        private readonly string _tableName;
        private readonly string _sessionId;

        private readonly DataTable _dt = new DataTable("params");

        private void Load()
        {
            using (SqlConnection myConnection = new SqlConnection(_connString))
            {
                myConnection.Open();

                _dt.Rows.Clear();
                using (SqlDataAdapter a = new SqlDataAdapter(String.Format("SELECT * FROM {0} where prm_sess = '{1}'", _tableName, _sessionId), myConnection))
                {
                    a.Fill(_dt);                    
                }
               
                _dt.PrimaryKey = new[] { _dt.Columns["prm_id"] };
                _dt.Columns["prm_id"].AutoIncrement = true;
            }
        }

        public string PrintParams()
        {
            string result = "";

            if (_dt != null)
                foreach (DataRow dr in _dt.Rows)
                    result += String.Format("{2} name: [{0}]; value [{1}]", dr["prm_name"], dr["prm_value"], Environment.NewLine);

            return result;
        }

        public bool Exists(string paramName)
        {
            DataRow foundRow = _dt.Rows.Find(paramName.ToLower());
            return (foundRow != null);
        }

        public Dictionary<string, string> GetDictionary()
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            foreach (DataRow dr in _dt.Rows)
                dict.Add((string)dr["prm_name"], (string)dr["prm_value"]);

            return dict;
        }

        public bool CreateStringParam(string paramName, string defaultValue)
        {
            return CreateParam(paramName, defaultValue, true);
        }

        public bool CreateBooleanParam(string paramName, Boolean defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(), true);
        }

        public bool CreateIntegerParam(string paramName, Int32 defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(), true);
        }

        public bool CreateInt64Param(string paramName, Int64 defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(), true);
        }

        public bool CreateDecimalParam(string paramName, Decimal defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(CultureInfo.InvariantCulture), true);
        }

        public bool CreateDoubleParam(string paramName, Double defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(CultureInfo.InvariantCulture), true);
        }

        public bool CreateDateTimeParam(string paramName, DateTime defaultValue)
        {
            return CreateParam(paramName, defaultValue.ToString(CultureInfo.InvariantCulture), true);
        }

        private bool CreateParam(string paramName, string paramValue, bool addIfNotFound = false)
        {
            DataRow[] foundRow = _dt.Select(String.Format("prm_name = '{0}'", paramName.ToLower()));
            if (foundRow.Length == 0)
            {
                if (addIfNotFound)
                {
                    DataRow newDataRow = _dt.NewRow();
                    newDataRow["prm_name"] = paramName.ToLower();
                    newDataRow["prm_value"] = paramValue;
                    _dt.Rows.Add(newDataRow);

                    using (SqlConnection myConnection = new SqlConnection(_connString))
                    {
                        myConnection.Open();

                        string sql = String.Format("insert into {0} (prm_name, prm_value, prm_sess) values ('{1}', '{2}', '{3}')",
                                                       _tableName, paramName.ToLower(), paramValue, _sessionId);

                        SqlCommand sc = new SqlCommand(sql, myConnection);
                        sc.ExecuteNonQuery();
                    }

                    //Load();
                }
                else
                    throw new Exception(String.Format("Params '{0}' not found!", paramName));
            }

            return true;
        }

        private bool SetParam(string paramName, string paramValue, bool addIfNotFound = false)
        {
            DataRow[] foundRow = _dt.Select(String.Format("prm_name = '{0}'", paramName.ToLower()));
            if (foundRow.Length > 0)
            {
                foundRow[0]["prm_value"] = paramValue;
               _dt.AcceptChanges();

                using (SqlConnection myConnection = new SqlConnection(_connString))
                {
                    myConnection.Open();

                    string sql = String.Format("update {0} set prm_value = '{1}' where prm_sess = '{2}' and prm_name = '{3}' ",
                                                   _tableName, paramValue, _sessionId, paramName.ToLower());

                    SqlCommand sc = new SqlCommand(sql, myConnection);
                    sc.ExecuteNonQuery();
                }

                //Load();
            }
            else
            {
                if (addIfNotFound)
                    CreateParam(paramName, paramValue, true);
                else
                    throw new Exception(String.Format("Params '{0}' not found!", paramName));
            }

            return true;
        }

        private string GetParam(string paramName)
        {
            string paramvalue;
            DataRow[] foundRow = _dt.Select(String.Format("prm_name = '{0}'", paramName.ToLower()));

            if (foundRow.Length > 0)
                paramvalue = foundRow[0]["prm_value"].ToString();
            else
                throw new Exception(String.Format("Params '{0}' not found!", paramName));
            return paramvalue;
        }

        public string GetStringParam(string paramName)
        {
            return GetParam(paramName);
        }

        public Int32 GetIntegerParam(string paramName)
        {
            Int32 value = Convert.ToInt32(GetParam(paramName));
            return value;
        }

        public Boolean GetBooleanParam(string paramName)
        {
            Boolean value = Convert.ToBoolean(GetParam(paramName));
            return value;
        }

        public Int64 GetInt64Param(string paramName)
        {
            Int64 value = Convert.ToInt64(GetParam(paramName));
            return value;
        }

        public Decimal GetDecimalParam(string paramName)
        {
            Decimal value = Convert.ToDecimal(GetParam(paramName));
            return value;
        }

        public Double GetDoubleParam(string paramName)
        {
            Double value = Convert.ToDouble(GetParam(paramName));
            return value;
        }

        public DateTime GetDateTimeParam(string paramName)
        {
            DateTime value = Convert.ToDateTime(GetParam(paramName));
            return value;
        }

        public bool SetStringParam(string paramName, string paramvalue)
        {
            return SetParam(paramName, paramvalue);
        }

        public bool SetBooleanParam(string paramName, Boolean paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString());
        }

        public bool SetIntegerParam(string paramName, Int32 paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString());
        }

        public bool SetInt64Param(string paramName, Int64 paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString());
        }

        public bool SetDecimalParam(string paramName, Decimal paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString(CultureInfo.InvariantCulture));
        }

        public bool SetDoubleParam(string paramName, Double paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString(CultureInfo.InvariantCulture));
        }

        public bool SetDateTimeParam(string paramName, DateTime paramvalue)
        {
            return SetParam(paramName, paramvalue.ToString(CultureInfo.InvariantCulture));
        }

        public DBParams(string connString, string tableName, string sessionId)
        {
            _connString = connString;
            _tableName = tableName;
            _sessionId = sessionId;

            Load();
        }
    }
}
